package db;

import java.io.File;
import java.io.FileNotFoundException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;

import com.sun.crypto.provider.RSACipher;

public class DBCreator {
	public static void main(String[] args) throws ClassNotFoundException {
		createDBWithDummyData("database.db");
		//createDB("database.db");
		//showData("database.db");
	}
	
	public static void createDBWithDummyData(String dbName) throws ClassNotFoundException {
		createDB(dbName);
		insertDummyData(dbName);
	}

	private static void insertDummyData(String dbName)
			throws ClassNotFoundException {
		Class.forName("org.sqlite.JDBC");

		Connection connection = null;
		try {
			// create a database connection
			connection = DriverManager.getConnection("jdbc:sqlite:" + dbName);
			Statement statement = connection.createStatement();
			statement.setQueryTimeout(30); // set timeout to 30 sec.
			statement.executeUpdate(saveFileToString("db_dummy_data.txt"));
		} catch (SQLException e) {
			// if the error message is "out of memory",
			// it probably means no database file is found
			System.err.println(e.getMessage());
		} finally {
			try {
				if (connection != null)
					connection.close();
			} catch (SQLException e) {
				// connection close failed.
				System.err.println(e);
			}
		}
	}

	public static void createDB(String dbName) throws ClassNotFoundException {
		Class.forName("org.sqlite.JDBC");

		Connection connection = null;
		try {
			// create a database connection
			connection = DriverManager.getConnection("jdbc:sqlite:" + dbName);
			Statement statement = connection.createStatement();
			statement.setQueryTimeout(30); // set timeout to 30 sec.
			statement.executeUpdate(saveFileToString("db_statements.txt"));
		} catch (SQLException e) {
			// if the error message is "out of memory",
			// it probably means no database file is found
			System.err.println(e.getMessage());
		} finally {
			try {
				if (connection != null)
					connection.close();
			} catch (SQLException e) {
				// connection close failed.
				System.err.println(e);
			}
		}
	}

	private static String saveFileToString(String filename) {
		File file = new File(filename);
		StringBuilder sb = new StringBuilder();
		try {
			Scanner scanner = new Scanner(file);
			while (scanner.hasNextLine()) {
				sb.append(scanner.nextLine());
			}
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		}
		String result = sb.toString();
		return result;
	}
	
	private static void showData(String dbName) throws ClassNotFoundException {
		Class.forName("org.sqlite.JDBC");

		Connection connection = null;
		try {
			// create a database connection
			connection = DriverManager.getConnection("jdbc:sqlite:" + dbName);
			Statement statement = connection.createStatement();
			statement.setQueryTimeout(30); // set timeout to 30 sec.
			statement.executeUpdate("PRAGMA foreign_keys = ON");
			ResultSet rs = statement.executeQuery("select * from Todo");
			System.out.println("Todo:");
			while(rs.next()) {
				System.out.println(rs.getInt("id"));
				System.out.println(rs.getString("name"));
				System.out.println(rs.getString("description"));
				System.out.println(rs.getString("start"));
				System.out.println(rs.getString("end"));
				System.out.println(rs.getString("category_name"));
				System.out.println("");
			}
			rs = statement.executeQuery("select * from Category");
			System.out.println("Category:");
			while(rs.next()) {
				System.out.println(rs.getString("name"));
				System.out.println(rs.getString("color"));
				System.out.println("");
			}
		} catch (SQLException e) {
			// if the error message is "out of memory",
			// it probably means no database file is found
			System.err.println(e.getMessage());
		} finally {
			try {
				if (connection != null)
					connection.close();
			} catch (SQLException e) {
				// connection close failed.
				System.err.println(e);
			}
		}
	}
}
